﻿using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.Xml;

namespace sqlmanage.common
{
    internal class dbosqlserver : dbo
    {
        public override void xmlsave(System.Collections.Hashtable hashtable)
        {
            string server = hashtable["server"].ToString();
            string user = hashtable["user"].ToString();
            string password = hashtable["password"].ToString();
            string type = hashtable["type"].ToString();
            string shujuku = hashtable["shujuku"].ToString();

            string strPath = Application.StartupPath + "\\Config" + "\\sql.xml";
            XmlDocument doc = new XmlDocument();
            doc.Load(strPath);
            var rootnode = doc.SelectSingleNode("root");
            XmlNodeList nodeList = rootnode.ChildNodes;

            XmlElement xmlElement = doc.CreateElement("sqlserver");
            xmlElement.SetAttribute("server", server);
            xmlElement.SetAttribute("user", user);
            xmlElement.SetAttribute("password", password);
            xmlElement.SetAttribute("shujuku", shujuku);

            rootnode.AppendChild(xmlElement);

            doc.Save(strPath);
        }

        public override void connect_database(System.Collections.Hashtable hashtable)
        {
            string server = hashtable["server"].ToString();
            string user = hashtable["user"].ToString();
            string password = hashtable["password"].ToString();

            string shujuku = hashtable["shujuku"].ToString();

            TreeView treeView = hashtable["treeView"] as TreeView;
            DataGridView dataGridView = hashtable["dataGridView"] as DataGridView;
            //string type = hashtable["type"].ToString();

            string strCon = "";
            if (!string.IsNullOrEmpty(user))
            {
                strCon = "server=" + server + ";database=;uid=" + user + ";pwd=" + password + ";";
            }
            else
            {
                strCon = "Server = " + server + "; Database = master; Trusted_Connection = True;";
            }

            SqlServerHelper.strConnString = strCon;
            string strSql = "select * from sys.sysdatabases";

            var sqlDataReader = SqlServerHelper.GetReader(strSql);

            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("数据库");

            while (sqlDataReader.Read())
            {
                DataRow dataRow = dataTable.NewRow();
                dataRow[0] = sqlDataReader[0].ToString();
                dataTable.Rows.Add(dataRow);
                if (string.IsNullOrEmpty(shujuku))
                {
                    TreeNode treeNode = new TreeNode();
                    treeNode.Text = sqlDataReader[0].ToString();
                    treeNode.Tag = "database";
                    treeNode.ImageIndex = 2;
                    treeNode.SelectedImageIndex = 3;
                    treeView.Nodes[0].Nodes.Add(treeNode);
                }
                else if (sqlDataReader[0].ToString().ToLower() == shujuku.ToLower())
                {
                    TreeNode treeNode = new TreeNode();
                    treeNode.Text = sqlDataReader[0].ToString();
                    treeNode.Tag = "database";
                    treeNode.ImageIndex = 2;
                    treeNode.SelectedImageIndex = 3;
                    treeView.Nodes[0].Nodes.Add(treeNode);

                }
            }
            treeView.Nodes[0].ExpandAll();
            sqlDataReader.Close();
            dataGridView.DataSource = dataTable;
        }
        public override object ExecuteAlone(string Query)
        {

            return SqlServerHelper.ExecuteAlone(Query);
        }
        public override DataTable ExcuteSql(string strSql)
        {
            SqlDataReader sqlDataReader = SqlServerHelper.GetReader(strSql);

            DataTable dataTable = new DataTable();

            for (int i = 0; i < sqlDataReader.FieldCount; i++)
            {
                dataTable.Columns.Add(sqlDataReader.GetName(i));
            }

            while (sqlDataReader.Read())
            {
                DataRow dataRow = dataTable.NewRow();
                for (int i = 0; i < sqlDataReader.FieldCount; i++)
                {
                    dataRow[i] = sqlDataReader[i].ToString();
                }

                dataTable.Rows.Add(dataRow);
            }

            //dataTable = SqlServerHelper.DealTable_Str(dataTable);

            sqlDataReader.Close();
            return dataTable;
        }



        public override StringBuilder Create(string database, string table)
        {
            string strSql = "use " + database + ";select   a.[name] as lieming,a.colstat as zizeng,b.[name] as leixing    from   syscolumns a,systypes b  where   a.id=object_id('"
                        + database + ".." + table + "') and a.xtype=b.xtype and    b.[name]<>'sysname'";
            DataTable dataTable = SqlServerHelper.getDataTable(strSql);

            string strFields = "";
            string strValues = "";

            foreach (DataRow dataRow in dataTable.Rows)
            {
                string field = dataRow["lieming"].ToString();
                string type = dataRow["leixing"].ToString();
                string zizeng = dataRow["zizeng"].ToString();
                //string nullvalue = dataRow["null"].ToString();
                //string key = dataRow["key"].ToString();
                //string extra = dataRow["extra"].ToString();
                //string comment = dataRow["comment"].ToString();

                //if (extra.IndexOf("auto_increment") != -1)
                //{
                //    continue;
                //}
                if (zizeng == "1")
                {
                    continue;
                }

                strFields += "" + field + ",";
                strValues += "1,";
            }

            //foreach (DataColumn dc in dt.Columns)
            //{
            //    strFields += "`" + dc.ColumnName + "`,";
            //    strValues += "'1',";

            //}
            StringBuilder stringBuilder = new StringBuilder();
            if (strValues.Trim() != "")
            {
                strFields = strFields.Substring(0, strFields.Length - 1);
                strValues = strValues.Substring(0, strValues.Length - 1);

                stringBuilder.Append("use " + database + "; insert into " + table + "(" + strFields + ") values(" + strValues + ")\n");
            }
            return stringBuilder;
        }
    }
}